package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import constants.Constants;
import domain.price.Price;

public class PriceDao {
	FileDao fileDao = new FileDao();
	
	String dbUrl = Constants.DB_URL;
	String dbUsername = Constants.DB_USERNAME;
	String dbPassword = Constants.DB_PASSWORD;
	
	public Price getPriceDetails(int fileNo){
		Price result = new Price();
		String query = "select Year, Month, Breed_Cows, Enter_Heifer, Heifer_Price, Cull_Price, Milk_Price from dbo.DVIPV_PRICE where file_no = "+fileNo;
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				int year = rs.getInt("Year");
				int month = rs.getInt("Month");
				result.getServe()[((year-1)*12)+month] = rs.getInt("Breed_Cows");
				result.getEnter()[((year-1)*12)+month] = rs.getInt("Enter_Heifer");
				result.getP_heifer()[((year-1)*12)+month] = rs.getDouble("Heifer_Price");
				result.getP_bodyweight()[((year-1)*12)+month] = rs.getDouble("Cull_Price");
				result.getP_milk()[((year-1)*12)+month] = rs.getDouble("Milk_Price");
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public boolean updatePriceDetails(int fileNo, Price obj){
		boolean result = true;
		
		String query = "update dbo.DVIP_IN_PRICE set Breed_Cows = ?, Enter_Heifer = ?, Heifer_Price = ?, Cull_Price = ?, Milk_Price = ? where file_no = ? and Year = ? and Month = ?";
		
		Connection sqlConnection = null;
		PreparedStatement pStmt = null;
		
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			
			for(int year=1; year<=2; year++){
				for(int month=1; month<=12; month++){
					int colIndex = 1;
					pStmt.setInt(colIndex++, obj.getServe()[((year-1)*12)+month]);
					pStmt.setInt(colIndex++, obj.getEnter()[((year-1)*12)+month]);
					pStmt.setDouble(colIndex++, obj.getP_heifer()[((year-1)*12)+month]);
					pStmt.setDouble(colIndex++, obj.getP_bodyweight()[((year-1)*12)+month]);
					pStmt.setDouble(colIndex++, obj.getP_milk()[((year-1)*12)+month]);
					
					pStmt.setInt(colIndex++, fileNo);
					pStmt.setInt(colIndex++, year);
					pStmt.setInt(colIndex++, month);
					
					pStmt.addBatch();
				}
			}
			
			//pStmt.executeUpdate();
			pStmt.executeBatch();
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			result = false;
			e.printStackTrace();
		} catch (ClassNotFoundException e1) {
			result = false;
			e1.printStackTrace();
		}
		
		return result;
	}

}
